﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using IBM.Data.DB2.iSeries;

namespace Unimaq.FacturaDBS_Spring.ADO
{
    public class DetalleFactura_UHAlquiler_ADO
    {
        #region Local
        public List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> ListarFacturasAlquileresFAMA(Util.ContextoDB ctx)
        {
            //SqlCommand cmd = new SqlCommand("USP_LISTAR_FACTURA_DBS_ALQUILER_FAMA", ctx.GetConexion(), ctx.GetTx());

            SqlCommand cmd =
                new SqlCommand(
                        "select " +
                            "cab.NUMORI, cab.SUNTIP, cab.SUNPRE, cab.SUNNUM," +
                            "cab.CENCOS as 'CABCENCOS'," +
                            "cab.TIPFORM,CAB.VALAF16,cab.MONEDA, CAB.TIPCAM," +
                            "det.NUMORI AS 'DETNUMORI',det.ANODOC," +
                            "det.TIPDOC, det.NUMSEC," +
                            "det.ACCION, det.LINEAD," +
                            "det.IMPORTE, det.CENCOS,cab.ECODE1, CAB.ECODE2, ECODE3," +
                            "det.ENCODE, det.FILL01 " +
                        "from " +
                            "dbo.UFPFAHD0 cab " +
                        "inner join dbo.UFPFADT0 det " +
                            "on (cab.NUMORI = det.NUMORI AND cab.NUMPAG = det.NUMPAG)" +
                        "where 	" +
                            " cab.CENCOS = 'UH' and" +
                            " cab.NUMORI like 'FAMA%' and" +
                            " det.NUMSEC > 10 and" +
                            " det.ACCION = '00' and" +
                            " det.ENCODE <> 'GLOSA'" +
                            " and (cab.SUNPRE <> 0 and cab.SUNNUM <> 0)" +
                        " order by cab.NUMORI, cab.SUNTIP, cab.SUNPRE, cab.SUNNUM, det.NUMSEC", ctx.GetConexion(), ctx.GetTx());

            DataTable dtResult = new DataTable();
            dtResult.Load(cmd.ExecuteReader());

            List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> result =
                new List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres>();

            foreach (DataRow fila in dtResult.Rows)
            {
                Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres hdAlqFAMA =
                    new Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres();

                hdAlqFAMA.Numori = fila["NUMORI"].ToString();
                hdAlqFAMA.Suntip = fila["SUNTIP"].ToString();
                hdAlqFAMA.Sunpre = decimal.Parse(fila["SUNPRE"].ToString());
                hdAlqFAMA.Sunnum = decimal.Parse(fila["SUNNUM"].ToString());
                hdAlqFAMA.Cencos = fila["CABCENCOS"].ToString();
                hdAlqFAMA.Tipform = fila["TIPFORM"].ToString();
                hdAlqFAMA.Valaf16 = decimal.Parse(fila["VALAF16"].ToString());
                hdAlqFAMA.Moneda = fila["MONEDA"].ToString();
                hdAlqFAMA.Tipcam = decimal.Parse(fila["TIPCAM"].ToString());
                hdAlqFAMA.DetNumori = fila["DETNUMORI"].ToString();
                hdAlqFAMA.Anodoc = decimal.Parse(fila["ANODOC"].ToString());
                hdAlqFAMA.Tipdoc = fila["TIPDOC"].ToString();
                hdAlqFAMA.Numsec = decimal.Parse(fila["NUMSEC"].ToString());
                hdAlqFAMA.Accion = fila["ACCION"].ToString();
                hdAlqFAMA.Linead = fila["LINEAD"].ToString();
                hdAlqFAMA.Importe = decimal.Parse(fila["IMPORTE"].ToString());
                hdAlqFAMA.DetCencos = fila["CENCOS"].ToString();
                hdAlqFAMA.Ecode1 = fila["ECODE1"].ToString();
                hdAlqFAMA.Ecode2 = fila["ECODE2"].ToString();
                hdAlqFAMA.Ecode3 = fila["ECODE3"].ToString();
                hdAlqFAMA.Encode = fila["ENCODE"].ToString();
                hdAlqFAMA.Fill01 = fila["FILL01"].ToString();

                result.Add(hdAlqFAMA);
            }

            return result;
        }

        public List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> ListarFacturasAlquileresNOFAMA(Util.ContextoDB ctx)
        {
            //SqlCommand cmd = new SqlCommand("USP_LISTAR_FACTURA_DBS_ALQUILER_NOFAMA", ctx.GetConexion(), ctx.GetTx());

            SqlCommand cmd =
                new SqlCommand(
                        "select " +
                            "cab.NUMORI, cab.SUNTIP, cab.SUNPRE, cab.SUNNUM," +
                            "cab.CENCOS as 'CABCENCOS'," +
                            "cab.TIPFORM,CAB.VALAF16,cab.MONEDA, CAB.TIPCAM," +
                            "det.NUMORI AS 'DETNUMORI',det.ANODOC," +
                            "det.TIPDOC, det.NUMSEC," +
                            "det.ACCION, det.LINEAD," +
                            "det.IMPORTE, det.CENCOS,cab.ECODE1, CAB.ECODE2, ECODE3," +
                            "det.ENCODE, det.FILL01 " +
                        " from " +
                            "dbo.UFPFAHD0 cab " +
                        " inner join dbo.UFPFADT0 det " +
                           "on (cab.NUMORI = det.NUMORI AND cab.NUMPAG = det.NUMPAG)" +
                        " where " +
                            " cab.CENCOS = 'UH' and " +
                            " cab.NUMORI NOT LIKE 'FAMA%' " +
                            " and det.NUMSEC > 19 and " +
                            " det.ACCION = '00' " +
                            " --and det.ENCODE <> 'GLOSA' " +
                            " and (cab.SUNPRE <> 0 and cab.SUNNUM <> 0) " +
                        " order by cab.NUMORI, cab.SUNTIP, cab.SUNPRE, cab.SUNNUM, det.NUMSEC"
                    , ctx.GetConexion(), ctx.GetTx());


            DataTable dtResult = new DataTable();
            dtResult.Load(cmd.ExecuteReader());

            List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> result =
                new List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres>();

            foreach (DataRow fila in dtResult.Rows)
            {
                Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres hdAlqFAMA =
                    new Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres();

                hdAlqFAMA.Numori = fila["NUMORI"].ToString();
                hdAlqFAMA.Suntip = fila["SUNTIP"].ToString();
                hdAlqFAMA.Sunpre = decimal.Parse(fila["SUNPRE"].ToString());
                hdAlqFAMA.Sunnum = decimal.Parse(fila["SUNNUM"].ToString());
                hdAlqFAMA.Cencos = fila["CABCENCOS"].ToString();
                hdAlqFAMA.Tipform = fila["TIPFORM"].ToString();
                hdAlqFAMA.Valaf16 = decimal.Parse(fila["VALAF16"].ToString());
                hdAlqFAMA.Moneda = fila["MONEDA"].ToString();
                hdAlqFAMA.Tipcam = decimal.Parse(fila["TIPCAM"].ToString());
                hdAlqFAMA.DetNumori = fila["DETNUMORI"].ToString();
                hdAlqFAMA.Anodoc = decimal.Parse(fila["ANODOC"].ToString());
                hdAlqFAMA.Tipdoc = fila["TIPDOC"].ToString();
                hdAlqFAMA.Numsec = decimal.Parse(fila["NUMSEC"].ToString());
                hdAlqFAMA.Accion = fila["ACCION"].ToString();
                hdAlqFAMA.Linead = fila["LINEAD"].ToString();
                hdAlqFAMA.Importe = decimal.Parse(fila["IMPORTE"].ToString());
                hdAlqFAMA.DetCencos = fila["CENCOS"].ToString();
                hdAlqFAMA.Ecode1 = fila["ECODE1"].ToString();
                hdAlqFAMA.Ecode2 = fila["ECODE2"].ToString();
                hdAlqFAMA.Ecode3 = fila["ECODE3"].ToString();
                hdAlqFAMA.Encode = fila["ENCODE"].ToString();
                hdAlqFAMA.Fill01 = fila["FILL01"].ToString();

                result.Add(hdAlqFAMA);
            }

            return result;
        } 
        #endregion

        #region IBM AS400
        public List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> ListarFacturasAlquileresFAMA_AS400(Util.ContextoDBAS400 ctx, DateTime fechaEmisionInicio, DateTime fechaEmisionFin)
        {
            //SqlCommand cmd = new SqlCommand("USP_LISTAR_FACTURA_DBS_ALQUILER_FAMA", ctx.GetConexion(), ctx.GetTx());

            iDB2Command cmd =
                new iDB2Command(
                    string.Format(
                        "select " +
                            "cab.NUMORI, cab.SUNTIP, cab.SUNPRE, cab.SUNNUM," +
                            "cab.CENCOS as \"CABCENCOS\", cab.FECEMI, cab.OFIC," +
                            "cab.TIPFORM,cab.VALAF16, cab.IMPOR16, cab.MONEDA, cab.TIPCAM," +
                            "det.NUMORI AS \"DETNUMORI\",det.ANODOC," +
                            "det.TIPDOC, det.NUMSEC," +
                            "det.ACCION, det.LINEAD," +
                            "det.IMPORTE, det.CENCOS,cab.ECODE1, cab.ECODE2, cab.ECODE3," +
                            "det.ENCODE, det.FILL01 " +
                        "from " +
                            "LIBR09.UFPFAHD0 cab " +
                        "inner join LIBR09.UFPFADT0 det " +
                            "on (cab.NUMORI = det.NUMORI AND cab.NUMPAG = det.NUMPAG)" +
                        "where 	" +
                            " (cab.SUNPRE <> 0 AND cab.SUNNUM <> 0 ) and" +
                            " (cab.FECEMI >= '{0}' AND cab.FECEMI <= '{1}') and" +
                            " cab.NUMPAG = 1 and" +
                            " cab.CENCOS = 'UH' and" +
                            " cab.NUMORI like 'FAMA%' and" +
                            " det.NUMSEC > 10 and" +
                            " det.ACCION = '00' and" +
                            " det.ENCODE <> 'GLOSA'" +
                        " order by cab.NUMORI, det.NUMSEC",
                        fechaEmisionInicio.ToString("yyyyMMdd"),
                        fechaEmisionFin.ToString("yyyyMMdd"))
                     , ctx.GetConexion(), ctx.GetTx());

            DataTable dtResult = new DataTable();
            dtResult.Load(cmd.ExecuteReader());

            List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> result =
                new List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres>();

            foreach (DataRow fila in dtResult.Rows)
            {
                Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres hdAlqFAMA =
                    new Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres();

                hdAlqFAMA.Numori = fila["NUMORI"].ToString();
                hdAlqFAMA.Suntip = fila["SUNTIP"].ToString();
                hdAlqFAMA.Sunpre = decimal.Parse(fila["SUNPRE"].ToString());
                hdAlqFAMA.Sunnum = decimal.Parse(fila["SUNNUM"].ToString());
                hdAlqFAMA.Cencos = fila["CABCENCOS"].ToString();
                hdAlqFAMA.Fecemi = fila["FECEMI"].ToString();
                hdAlqFAMA.Ofic = decimal.Parse(fila["OFIC"].ToString());
                hdAlqFAMA.Tipform = fila["TIPFORM"].ToString();
                hdAlqFAMA.Valaf16 = decimal.Parse(fila["VALAF16"].ToString());
                hdAlqFAMA.Impor16 = decimal.Parse(fila["IMPOR16"].ToString());
                hdAlqFAMA.Moneda = fila["MONEDA"].ToString();
                hdAlqFAMA.Tipcam = decimal.Parse(fila["TIPCAM"].ToString());
                hdAlqFAMA.DetNumori = fila["DETNUMORI"].ToString();
                hdAlqFAMA.Anodoc = decimal.Parse(fila["ANODOC"].ToString());
                hdAlqFAMA.Tipdoc = fila["TIPDOC"].ToString();
                hdAlqFAMA.Numsec = decimal.Parse(fila["NUMSEC"].ToString());
                hdAlqFAMA.Accion = fila["ACCION"].ToString();
                hdAlqFAMA.Linead = fila["LINEAD"].ToString();
                hdAlqFAMA.Importe = decimal.Parse(fila["IMPORTE"].ToString());
                hdAlqFAMA.DetCencos = fila["CENCOS"].ToString();
                hdAlqFAMA.Ecode1 = fila["ECODE1"].ToString();
                hdAlqFAMA.Ecode2 = fila["ECODE2"].ToString();
                hdAlqFAMA.Ecode3 = fila["ECODE3"].ToString();
                hdAlqFAMA.Encode = fila["ENCODE"].ToString();
                hdAlqFAMA.Fill01 = fila["FILL01"].ToString();

                result.Add(hdAlqFAMA);
            }

            return result;
        }

        public List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> ListarFacturasAlquileresNOFAMA_AS400(Util.ContextoDBAS400 ctx, DateTime fechaEmisionInicio, DateTime fechaEmisionFin)
        {
            //SqlCommand cmd = new SqlCommand("USP_LISTAR_FACTURA_DBS_ALQUILER_NOFAMA", ctx.GetConexion(), ctx.GetTx());

            iDB2Command cmd =
                new iDB2Command(
                    string.Format(
                        "select " +
                            "cab.NUMORI, cab.SUNTIP, cab.SUNPRE, cab.SUNNUM," +
                            "cab.CENCOS as \"CABCENCOS\", cab.FECEMI, cab.OFIC," +
                            "cab.TIPFORM, cab.VAVTA1, cab.VALAF16, cab.IMPOR16, cab.MONEDA, cab.TIPCAM," +
                            "det.NUMORI AS \"DETNUMORI\",det.ANODOC," +
                            "det.TIPDOC, det.NUMSEC," +
                            "det.ACCION, det.LINEAD," +
                            "det.IMPORTE, det.CENCOS,cab.ECODE1, cab.ECODE2, cab.ECODE3," +
                            "det.ENCODE, det.FILL01 " +
                        " from " +
                            "LIBR09.UFPFAHD0 cab " +
                        " inner join LIBR09.UFPFADT0 det " +
                           "on (cab.NUMORI = det.NUMORI AND cab.NUMPAG = det.NUMPAG)" +
                        " where " +
                            " (cab.SUNPRE <> 0 AND cab.SUNNUM <> 0 ) and" +
                            " (cab.FECEMI >= '{0}' AND cab.FECEMI <= '{1}') and" +
                            " cab.NUMPAG = 1 and" +
                            " cab.CENCOS = 'UH' and " +
                            " cab.NUMORI NOT LIKE 'FAMA%' " +
                            " and det.NUMSEC > 19 and " +
                            " det.ACCION = '00' " +
                        " order by cab.NUMORI, det.NUMSEC",
                        fechaEmisionInicio.ToString("yyyyMMdd"),
                        fechaEmisionFin.ToString("yyyyMMdd"))
                    , ctx.GetConexion(), ctx.GetTx());


            DataTable dtResult = new DataTable();
            dtResult.Load(cmd.ExecuteReader());

            List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres> result =
                new List<Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres>();

            foreach (DataRow fila in dtResult.Rows)
            {
                Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres hdAlqFAMA =
                    new Entidades.CentroCosto.UH.DBS_Head_Detail_UHAlquileres();

                hdAlqFAMA.Numori = fila["NUMORI"].ToString();
                hdAlqFAMA.Suntip = fila["SUNTIP"].ToString();
                hdAlqFAMA.Sunpre = decimal.Parse(fila["SUNPRE"].ToString());
                hdAlqFAMA.Sunnum = decimal.Parse(fila["SUNNUM"].ToString());
                hdAlqFAMA.Cencos = fila["CABCENCOS"].ToString();
                hdAlqFAMA.Fecemi = fila["FECEMI"].ToString();
                hdAlqFAMA.Ofic = decimal.Parse(fila["OFIC"].ToString());
                hdAlqFAMA.Tipform = fila["TIPFORM"].ToString();
                hdAlqFAMA.Valaf16 = decimal.Parse(fila["VALAF16"].ToString());
                hdAlqFAMA.Impor16 = decimal.Parse(fila["IMPOR16"].ToString());
                hdAlqFAMA.Vavta1 = decimal.Parse(fila["VAVTA1"].ToString());
                hdAlqFAMA.Moneda = fila["MONEDA"].ToString();
                hdAlqFAMA.Tipcam = decimal.Parse(fila["TIPCAM"].ToString());
                hdAlqFAMA.DetNumori = fila["DETNUMORI"].ToString();
                hdAlqFAMA.Anodoc = decimal.Parse(fila["ANODOC"].ToString());
                hdAlqFAMA.Tipdoc = fila["TIPDOC"].ToString();
                hdAlqFAMA.Numsec = decimal.Parse(fila["NUMSEC"].ToString());
                hdAlqFAMA.Accion = fila["ACCION"].ToString();
                hdAlqFAMA.Linead = fila["LINEAD"].ToString();
                hdAlqFAMA.Importe = decimal.Parse(fila["IMPORTE"].ToString());
                hdAlqFAMA.DetCencos = fila["CENCOS"].ToString();
                hdAlqFAMA.Ecode1 = fila["ECODE1"].ToString();
                hdAlqFAMA.Ecode2 = fila["ECODE2"].ToString();
                hdAlqFAMA.Ecode3 = fila["ECODE3"].ToString();
                hdAlqFAMA.Encode = fila["ENCODE"].ToString();
                hdAlqFAMA.Fill01 = fila["FILL01"].ToString();

                result.Add(hdAlqFAMA);
            }

            return result;
        }  
        #endregion
    }
}
